-- Creating Database
CREATE DATABASE MelodyHwb;
USE MelodyHwbDB;

-- Creating Tables
CREATE TABLE Products 
(
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(100) NOT NULL,
    Description TEXT,
    Price DECIMAL(10, 2) NOT NULL,
    StockQuantity INT CHECK (StockQuantity >= 0)
);

CREATE TABLE Customers 
(
    CustomerID INT PRIMARY KEY,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    Email VARCHAR(100) UNIQUE NOT NULL,
    PhoneNumber VARCHAR(13),
    PostCode VARCHAR(8),
	
-- Additional Customer details can be added
);

CREATE TABLE Orders 
(
    OrderID INT PRIMARY KEY,
    CustomerID INT FOREIGN KEY REFERENCES Customers(CustomerID),
    OrderDate DATETIME NOT NULL,
    TotalAmount DECIMAL(10, 2) NOT NULL,
    Status VARCHAR(20) DEFAULT 'Pending'
);

CREATE TABLE OrderItems 
(
    OrderItemID INT PRIMARY KEY,
    OrderID INT,
    ProductID INT,
    Quantity INT CHECK (Quantity > 0), 
    Price DECIMAL(10, 2),
    CONSTRAINT FK_OrderItems_Orders FOREIGN KEY (OrderID) REFERENCES Orders(OrderID),
    CONSTRAINT FK_OrderItems_Products FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);

CREATE TABLE MaintenanceServices
 (
    ServiceID INT PRIMARY KEY,
    CustomerID INT,
    InstrumentID INT,
    ServiceType VARCHAR(100) NOT NULL,
    ServiceDate DATETIME NOT NULL,
    ServiceStatus VARCHAR(20) DEFAULT 'Scheduled',
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID),
    FOREIGN KEY (InstrumentID) REFERENCES Instruments(InstrumentID)
);
CREATE TABLE Instruments 
(
    InstrumentID INT PRIMARY KEY,
    InstrumentName VARCHAR(100) NOT NULL,
-- Can add more relevant columns for instruments
);

-- Adding records to Customers table
INSERT INTO Customers (FirstName, LastName, Email, PhoneNumber, PostCode)
VALUES
   (1,'John','Doe','john.doe@email.com','+447934567890','SA2 2RE'),
   (2,'Jane','Smith','jane.smith@email.com','+447798763210','BN3 8LH'),
   (3,'Mike','Johnson','mike.johnson@email.com','+447955564567','CA1 2SR'),
   (4,'Emily','Brown','emily.brown@email.com','+447933446521','YO14 9BY'),
   (5,'David','Clark','david.clark@email.com','+447738889999','PO2 0ET'),
   (6,'Sophie','Williams','sophie.williams@email.com','+447035556666','GL52 2HT'),
   (7,'Robert','Miller','robert.miller@email.com','+447766778888','BH8 8SY'),
   (8,'Lisa','Taylor','lisa.taylor@email.com','+447065143333','CH4 8EF'),
   (9,'Daniel','Anderson','daniel.anderson@email.com','+447829990000','DA17 6DH'),
   (10,'Amy','White','amy.white@email.com','+447014853225','NG14 7HL');

-- Adding records to Products table
INSERT INTO Products (ProductID, ProductName, Description, Price, StockQuantity) VALUES
    (1, 'Electric Guitar', 'High-quality electric guitar for professionals', 599.99, 25),
    (2, 'Digital Keyboard', '88-key digital keyboard with weighted keys', 869.99, 21),
    (3, 'Acoustic Drum Set', 'Complete drum set with cymbals and hardware', 1199.99, 20),
    (4, 'Bass Guitar', '4-string bass guitar with active pickups', 699.79, 20),
    (5, 'Microphone', 'Condenser microphone for studio recording', 147.99, 65),
    (6, 'DJ Mixer', 'Professional 4-channel DJ mixer with effects', 459.99, 15),
    (7, 'Saxophone', 'Alto saxophone with gold lacquer finish', 859.20, 10),
    (8, 'Piano Bench', 'Adjustable piano bench with leather seat', 79.99, 25),
    (9, 'Guitar Strings', 'Set of high-quality steel guitar strings', 9.99, 100),
    (10, 'Audio Interface', 'USB audio interface for home recording', 129.99, 35);

-- Adding records to Instruments table
INSERT INTO Intruments (InstrumentID, InstrumentName) VALUES
    (1, 'Electric Guitar'),
    (2, 'Digital Keyboard'),
    (3, 'Acoustic Drum Set'),
    (4, 'Bass Guitar'),
    (5, 'Microphone'),
    (6, 'DJ Mixer'),
    (7, 'Saxophone'),
    (8, 'Grand Piano'),
    (9, 'Violin'),
    (10, 'Flute');

-- Adding records to MaintenanceServices table
INSERT INTO MaintenanceServices (ServiceID, CustomerID, InstrumentID, ServiceType, ServiceDate, ServiceStatus) VALUES
    (1, 1, 1, 'Tune-up', GETDATE(), 'Scheduled'),
    (2, 2, 2, 'Repair', GETDATE(), 'Completed'),
    (3, 3, 3, 'Cleaning', GETDATE(), 'Scheduled'),
    (4, 4, 4, 'String Replacement', GETDATE(), 'InProgress'),
    (5, 5, 5, 'Microphone Check', GETDATE(), 'Completed'),
    (6, 6, 6, 'Calibration', GETDATE(), 'Scheduled'),
    (7, 7, 7, 'Saxophone Reeds Replacement', GETDATE(), 'InProgress'),
    (8, 8, 8, 'Piano Tuning', GETDATE(), 'Completed'),
    (9, 9, 9, 'Violin Bow Rehair', GETDATE(), 'Scheduled'),
    (10, 10, 10, 'Flute Cleaning', GETDATE(), 'InProgress');

-- Adding records to Orders table
INSERT INTO Orders (CustomerID, OrderDate, TotalAmount, Status)
VALUES
    (1, GETDATE(), 150.99, 'Completed'),
    (2, GETDATE(), 200.50, 'Pending'),
    (3, GETDATE(), 300.75, 'Completed'),
    (1, GETDATE(), 50.25, 'Pending'),
    (2, GETDATE(), 180.00, 'Completed'),
    (3, GETDATE(), 75.50, 'Pending'),
    (1, GETDATE(), 120.75, 'Completed'),
    (2, GETDATE(), 90.00, 'Pending'),
    (3, GETDATE(), 500.25, 'Completed'),
    (1, GETDATE(), 80.50, 'Pending');

-- Adding records to OrderItems table
INSERT INTO OrderItems (OrderID, ProductID, Quantity, Price)
VALUES
    (1, 1, 2, (SELECT Price FROM Products WHERE ProductID = 1)),
    (2, 2, 1, (SELECT Price FROM Products WHERE ProductID = 2)),
    (3, 3, 3, (SELECT Price FROM Products WHERE ProductID = 3)),
    (4, 4, 1, (SELECT Price FROM Products WHERE ProductID = 4)),
    (5, 5, 2, (SELECT Price FROM Products WHERE ProductID = 5)),
    (6, 6, 1, (SELECT Price FROM Products WHERE ProductID = 6)),
    (7, 7, 1, (SELECT Price FROM Products WHERE ProductID = 7)),
    (8, 8, 1, (SELECT Price FROM Products WHERE ProductID = 8)),
    (9, 9, 5, (SELECT Price FROM Products WHERE ProductID = 9)),
    (10, 10, 1, (SELECT Price FROM Products WHERE ProductID = 10));


-- Sample Transaction for Order Processing

-- Begin the transaction
BEGIN TRANSACTION;

-- Variables for order details
DECLARE @OrderID INT;
DECLARE @ProductID INT;
DECLARE @Quantity INT;
DECLARE @ProductPrice DECIMAL(10, 2);

-- Assume you have a new order to process
-- Insert a new order
INSERT INTO Orders (CustomerID, OrderDate, TotalAmount, Status)
VALUES (4, GETDATE(), 0, 'Pending');

-- Get the OrderID of the newly inserted order
SET @OrderID = SCOPE_IDENTITY();

-- Example: Add items to the order
-- In a real-world scenario, this data would come from the user or application
SET @ProductID = 1;  -- Assuming product ID 1 is an Electric Guitar
SET @Quantity = 3;   -- Order 3 electric guitars
SET @ProductPrice = (SELECT Price FROM Products WHERE ProductID = @ProductID);

-- Insert item into OrderItems
INSERT INTO OrderItems (OrderID, ProductID, Quantity, Price)
VALUES (@OrderID, @ProductID, @Quantity, @ProductPrice);

-- Update inventory levels
UPDATE Products
SET StockQuantity = StockQuantity - @Quantity
WHERE ProductID = @ProductID;

-- Commit the transaction
COMMIT;

-- Example of rolling back the transaction (uncomment to simulate a rollback)
-- ROLLBACK;
